from PyCmpltrtok.common import sep
from typing import List
from typing import Optional
from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship

class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "user_account"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    # fullname: Mapped[Optional[str]]  # sqlalchemy.exc.CompileError: (in table 'user_account', column 'fullname'): VARCHAR requires a length on dialect mysql
    fullname: Mapped[Optional[str]] = mapped_column(String(50))
    addresses: Mapped[List["Address"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )
    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"

class Address(Base):
    __tablename__ = "address"
    id: Mapped[int] = mapped_column(primary_key=True)
    # email_address: Mapped[str]  # sqlalchemy.exc.CompileError: (in table 'address', column 'email_address'): VARCHAR requires a length on dialect mysql
    email_address: Mapped[str] = mapped_column(String(100))
    user_id: Mapped[int] = mapped_column(ForeignKey("user_account.id"))
    user: Mapped["User"] = relationship(back_populates="addresses")
    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"

from sqlalchemy import create_engine
engine = create_engine("mysql+mysqlconnector://root:yourpasswd@localhost:3306/for_programs", echo=True)

sep()
sep()
print(Base.__subclasses__())
sep()
sep()

r = Base.metadata.create_all(engine)
print(r)

"""
2024-04-05 13:13:03,524 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2024-04-05 13:13:03,524 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-04-05 13:13:03,524 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2024-04-05 13:13:03,524 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-04-05 13:13:03,525 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2024-04-05 13:13:03,525 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-04-05 13:13:03,525 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-05 13:13:03,525 INFO sqlalchemy.engine.Engine DESCRIBE `for_programs`.`user_account`
2024-04-05 13:13:03,525 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-04-05 13:13:03,526 INFO sqlalchemy.engine.Engine DESCRIBE `for_programs`.`address`
2024-04-05 13:13:03,526 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-04-05 13:13:03,527 INFO sqlalchemy.engine.Engine 
CREATE TABLE user_account (
        id INTEGER NOT NULL AUTO_INCREMENT, 
        name VARCHAR(30) NOT NULL, 
        fullname VARCHAR(50), 
        PRIMARY KEY (id)
)


2024-04-05 13:13:03,527 INFO sqlalchemy.engine.Engine [no key 0.00019s] {}
2024-04-05 13:13:03,562 INFO sqlalchemy.engine.Engine 
CREATE TABLE address (
        id INTEGER NOT NULL AUTO_INCREMENT, 
        email_address VARCHAR(100) NOT NULL, 
        user_id INTEGER NOT NULL, 
        PRIMARY KEY (id), 
        FOREIGN KEY(user_id) REFERENCES user_account (id)
)


2024-04-05 13:13:03,563 INFO sqlalchemy.engine.Engine [no key 0.00075s] {}
2024-04-05 13:13:03,620 INFO sqlalchemy.engine.Engine COMMIT
None
"""

"""
When running with tables existed:

2024-04-05 13:09:52,942 INFO sqlalchemy.engine.Engine SELECT DATABASE()
2024-04-05 13:09:52,942 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-04-05 13:09:52,943 INFO sqlalchemy.engine.Engine SELECT @@sql_mode
2024-04-05 13:09:52,943 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-04-05 13:09:52,943 INFO sqlalchemy.engine.Engine SELECT @@lower_case_table_names
2024-04-05 13:09:52,943 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-04-05 13:09:52,944 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-05 13:09:52,944 INFO sqlalchemy.engine.Engine DESCRIBE `for_programs`.`user_account`
2024-04-05 13:09:52,944 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-04-05 13:09:52,945 INFO sqlalchemy.engine.Engine DESCRIBE `for_programs`.`address`
2024-04-05 13:09:52,945 INFO sqlalchemy.engine.Engine [raw sql] {}
2024-04-05 13:09:52,946 INFO sqlalchemy.engine.Engine COMMIT
None
"""

from sqlalchemy.orm import Session

with Session(engine) as session:
    spongebob = User(
        name="spongebob",
        fullname="Spongebob Squarepants",
        addresses=[Address(email_address="spongebob@sqlalchemy.org")],
    )
    sandy = User(
        name="sandy",
        fullname="Sandy Cheeks",
        addresses=[
            Address(email_address="sandy@sqlalchemy.org"),
            Address(email_address="sandy@squirrelpower.org"),
        ],
    )
    patrick = User(name="patrick", fullname="Patrick Star")
    session.add_all([spongebob, sandy, patrick])
    session.commit()

"""
2024-04-05 13:13:03,641 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-04-05 13:13:03,644 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s)
2024-04-05 13:13:03,644 INFO sqlalchemy.engine.Engine [generated in 0.00069s] {'name': 'spongebob', 'fullname': 'Spongebob Squarepants'}
2024-04-05 13:13:03,647 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s)
2024-04-05 13:13:03,647 INFO sqlalchemy.engine.Engine [cached since 0.003416s ago] {'name': 'sandy', 'fullname': 'Sandy Cheeks'}
2024-04-05 13:13:03,648 INFO sqlalchemy.engine.Engine INSERT INTO user_account (name, fullname) VALUES (%(name)s, %(fullname)s)
2024-04-05 13:13:03,648 INFO sqlalchemy.engine.Engine [cached since 0.004908s ago] {'name': 'patrick', 'fullname': 'Patrick Star'}
2024-04-05 13:13:03,650 INFO sqlalchemy.engine.Engine INSERT INTO address (email_address, user_id) VALUES (%(email_address)s, %(user_id)s)
2024-04-05 13:13:03,651 INFO sqlalchemy.engine.Engine [generated in 0.00044s] {'email_address': 'spongebob@sqlalchemy.org', 'user_id': 1}
2024-04-05 13:13:03,652 INFO sqlalchemy.engine.Engine INSERT INTO address (email_address, user_id) VALUES (%(email_address)s, %(user_id)s)
2024-04-05 13:13:03,652 INFO sqlalchemy.engine.Engine [cached since 0.002262s ago] {'email_address': 'sandy@sqlalchemy.org', 'user_id': 2}
2024-04-05 13:13:03,653 INFO sqlalchemy.engine.Engine INSERT INTO address (email_address, user_id) VALUES (%(email_address)s, %(user_id)s)
2024-04-05 13:13:03,653 INFO sqlalchemy.engine.Engine [cached since 0.003168s ago] {'email_address': 'sandy@squirrelpower.org', 'user_id': 2}
2024-04-05 13:13:03,654 INFO sqlalchemy.engine.Engine COMMIT
"""
